"mr_gofast" (jake_berenshteyn)
07/14/2014 at 09:57 • Filed to: EXCEL
2
10|
"mr_gofast" (jake_berenshteyn)
07/14/2014 at 09:57 • Filed to: EXCEL | 2
| 10 |
i have a worksheet with many columns of data
one has regions
one has brand of railway companys
one has a number figure ( sorry im being ambigous - cant talk about work)
i need a formula to check the first 2 columns for my conditions ( i want this region and that brands totals out of all the totals.
ive tried
sumif
sumifs
countifs
counta
the top 2 seem better but im stumped.
so again if i want to show a total for all records that match my 2 conditions, how do i code it?
worst part is that i got the first 2 regions to work fine, 3rd region wont work at all. it gives me a zero for the output when its supposed to be a number and i cant figure out why
here have a hemi cuda
quarterlifecrisis
> mr_gofast
07/14/2014 at 10:00 |
|
No help on excel, have been out of the mix for awhile. I'm just here for the Plum Crazy 71.
Whoops, Plymouth. In-Violet, not Plum Crazy.
Alfalfa
> mr_gofast
07/14/2014 at 10:12 |
|
It's sumif, but you do need to put the "=" in front of it, at least in any Excel version I've used. So =sumif (A1:A5,">5") would give you the sum of all numbers between A1 and A5, that are greater than 5.
|
mr_gofast
> Alfalfa
07/14/2014 at 10:21 |
|
sumif if there is only one condition, there is 2 here so therfore =Sumifs
Jobjoris
> Alfalfa
07/14/2014 at 10:23 |
|
The conditions are on the first two columns so you'll have to use SUMIFS for multiple conditions. If I understand the question correct.
|
mr_gofast
> Jobjoris
07/14/2014 at 10:25 |
|
based on excel, first column is sum range (where im looking), second is condition range, 3rd is condition, 4thi s second condition range, 5 is second condition.
|
Jobjoris
> mr_gofast
07/14/2014 at 10:37 |
|
SUMIFS is just meant to get one sum range (column1, right?) using two conditions. Should be something like '=SUMIFS(A2:A10, B2:B10, "North", C2:C10, "RailNorth")' in which 'A2:A9' is the the sum you want to make (column1), 'B2:B9, "North"' is the first condition (so your column2 + it's condition) and 'C2:C9, "RailNorth" is the second (your column 4).
|
mr_gofast
> Jobjoris
07/14/2014 at 11:05 |
|
got it....turns out i had a space in front of the names of regions..excel therefore wouldnt match it.
duurtlang
> mr_gofast
07/14/2014 at 11:36 |
|
It helps if you don't add the text in the formula but a link to the text. It makes it more durable for when you want to change it as well. So, taking jobjoris' equation, I'd turn '=SUMIFS(A 2 :A1 0 , B 2 :B1 0 , " North ", C 2 :C1 0 , " RailNorth ")' into '=SUMIFS(A 3 :A1 1 , B 3 :B1 1 , A$1 , C 3 :C1 1 , C$1 )' with A1 and C1 the fields where you enter the text you want to look up and the $ signs so you can paste it in lower rows without adjusting the formula.
|
mr_gofast
> duurtlang
07/14/2014 at 11:38 |
|
awesome thsnks both of you!
The Compromiser
> mr_gofast
07/14/2014 at 18:05 |
|
sounds like a vlookup situation, but I only have used it for single look up and returns for another sheet to sum up.